import time
import geopandas as gpd
from shapely import wkb
from shapely.geometry import Point
from tools.lbs_utils import *

"""
对采用lbs数据的实时派单进行测试检验
不使用协程的形式
"""
logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(name)s - %(levelname)s - %(lineno)d: %(message)s')
logger = logging.getLogger(__name__)
table_name = 'travel_time'


# 模拟查询
def load_test_data():
    logger.info("load data")
    df_orders = pd.read_csv('data/meirong/sample_orders.csv')
    df_orders['lon'] = df_orders['lon'].astype(float).round(3)
    df_orders['lat'] = df_orders['lat'].astype(float).round(3)
    artisans = pd.read_csv('data/meirong/sample_artisans.csv')
    artisans['lon'] = artisans['lon'].astype(float).round(3)
    artisans['lat'] = artisans['lat'].astype(float).round(3)
    # 19点的订单
    # orders = orders[(orders['start_hour'] == 19) & (orders['start_hour'] == 20)].reset_index(drop=True)
    df_orders = df_orders[0:10]
    logger.debug('load orders:' + str(len(df_orders)))
    # 符合条件的手艺人
    # 由于候选手艺人由大雄提供，所以为了测试性能，可以随机选取100个美容手艺人
    # artisans = artisans[0:200]
    return df_orders, artisans


def gen_df_points(df_position):
    try:
        df_position['s_lon'], df_position['s_lat'] = zip(*df_position['start'].apply(position_to_points).to_list())
    except:
        return None, 5
    try:
        df_position['e_lon'], df_position['e_lat'] = zip(*df_position['end'].apply(position_to_points).to_list())
    except:
        return None, 6
    if df_position.duplicated().any():
        logger.warning('position is duplicates')
        # df_artisans = df_artisans.drop_duplicates().dropna()
    logger.debug('load position:' + str(len(df_position)))
    df_position['s_hex'] = list(df_position.apply(lambda x: Point(float(x['s_lon']), float(x['s_lat'])).wkb_hex, axis=1))
    df_position['e_hex'] = list(df_position.apply(lambda x: Point(float(x['e_lon']), float(x['e_lat'])).wkb_hex, axis=1))
    logger.debug('gen wkt points:' + str(len(df_position)))
    return df_position, 0


def hex_to_position(hex_str):
    point = wkb.loads(hex_str, hex=True)
    longitude = str(point.x)
    latitude = str(point.y)
    return longitude + "," + latitude


def wkt_to_position(we):
    point = wkb.loads(we, hex=True)
    result = None
    if point is not None:
        longitude = point.x
        latitude = point.y
        result = str(longitude) + "," + str(latitude)
    return result


# 查询位置与时间
query_travel_time = """
    select p1, p2, CONCAT(p1::text,p2::text,travel_type,start::text) as line_hex, start, travel_type, travel_time, distance from {0}
     where city_id='{1}' and CONCAT(p1::text,p2::text,travel_type,start::text) in ({2})
"""
# union的逐一查询近似速度(效率太低，废弃)
# query_speed = """
#     select distance/travel_time as speed from {0}
#      where city_id='{1}' and travel_type='{2}' and start='{3}'
#      order by ST_Distance_Sphere(p1, ST_GeomFromText('point({4})')),ST_Distance_Sphere(p2, ST_GeomFromText('point({5})'))
#     limit 1
# """

# 缺失了当前未查询点的位置，需要先记录
# 支持postgres的写法(group by携带额外字段不支持)
# 查出临近路线的计算近似时间
query_speed_all = """
    select distinct on (n.p1,n.p2) n.p1,n.p2,
     (ST_Distance(n.p1, n.p2)/ST_Distance(tt.p1, tt.p2)) * tt.travel_time as travel_time,
     (ST_Distance(n.p1, n.p2)/ST_Distance(tt.p1, tt.p2)) * tt.distance as distance
    from new_lines n left join {0} tt on n.city_id=tt.city_id where tt.city_id='{1}'
    and tt.travel_type='{2}' and n.update_key='{3}' and ST_Distance(tt.p1, tt.p2)>0 and ST_Distance(n.p1, n.p2)>0
    order by n.p1,n.p2,ST_Distance(n.p1, tt.p1),ST_Distance(n.p2, tt.p2),abs(tt.start-{4})
"""

model_redis = conn_redis()
# 限制最大时间为2小时
max_time = 60 * 60 * 2
min_time = 60 * 30
# 限制最大距离为100公里
max_distance = 100 * 1000


def get_line_hex(s_point):
    str_hex = str(s_point['s_hex']) + str(s_point['e_hex']) + str(s_point['type']) + str(s_point['hour'])
    return str_hex


def get_times(conn, city_id, df_points):
    travel_times = None
    if 'period' not in df_points.columns:
        df_points['period'] = 0
    if 'hour' not in df_points.columns:
        df_points['hour'] = time.localtime().tm_hour
    df_points['hour'] = df_points['hour'].astype(int, errors='ignore')
    if 'type' not in df_points.columns:
        df_points['type'] = 'bus'
    df_points['line_hex'] = df_points.apply(get_line_hex, axis=1)
    point_str = "'" + "','".join(df_points['line_hex']) + "'"
    time_sql = query_travel_time.format(table_name, city_id, point_str)
    try:
        all_times = pd.read_sql(time_sql, conn)
        logger.debug('get record from travel_times:' + str(len(all_times)))
    except Exception as e:
        logger.error(e)
        all_times = None
    # 不存在路程时间的地址
    # not_exists = list(set(p1_Points) - set(all_times['p1']))
    if all_times is not None:
        not_exists = df_points[~df_points['line_hex'].isin(all_times['line_hex'])]
        all_times['period'] = df_points[df_points['line_hex'].isin(all_times['line_hex'])]['period']
        all_times.drop('line_hex', axis=1, inplace=True)
    else:
        not_exists = df_points
    no_number = len(not_exists)
    if no_number > 0:
        city_name = get_city_value(city_id, False)
        no_exists_dicts = [{'origin': p['start'], 'destination': p['end'], 'city': city_name, 'cityd': city_name,
                            'time': p['hour'], 'travel_type': p['type'], 'period': p['period']} for i, p in not_exists.iterrows()]
        logger.debug('request amap:' + str(len(no_exists_dicts)))
        no_exists_json = list(map(json.dumps, no_exists_dicts))
        model_redis.lpush("disp_missing_point", *no_exists_json)
        # 从已有地址库中查询临近地址求近似时间
        add_times = batch_transit_time(no_exists_dicts)
        if add_times is not None:
            logger.debug('get amap:' + str(len(add_times)))
            add_times = gpd.GeoDataFrame.from_dict(add_times)
            add_times['p1'] = [Point(float(x.split(',')[0]), float(x.split(',')[1])) for x in add_times['p1']]
            add_times['p2'] = [Point(float(x.split(',')[0]), float(x.split(',')[1])) for x in add_times['p2']]
            add_times.set_geometry('p1', inplace=True)
            add_times.set_geometry('p2', inplace=True)
            add_times['p1'] = add_times['p1'].apply(create_wkt_element)
            add_times['p2'] = add_times['p2'].apply(create_wkt_element)
            add_times['city_id'] = city_id
            add_times['city_cd'] = get_city_value(city_id)
            if all_times is None or len(all_times) == 0:
                all_times = add_times
            else:
                all_times = all_times.append(add_times, ignore_index=True)
    if all_times is not None:
        # logger.debug(all_times['p1'].values[0:5])
        all_times['start'] = all_times['p1'].apply(wkt_to_position)
        all_times['end'] = all_times['p2'].apply(wkt_to_position)
        # travel_times = all_times.sort_values(['period', 'travel_time', 'distance']).reset_index(drop=True)
        travel_times = all_times
        logger.debug("gen travel_times: " + str(len(travel_times)))
    return travel_times


def get_travel_time(conn, city_id, p1, p2, start, city_name=None):
    """
    取得单条线路路程时间
    :param conn: 数据库连接
    :param city_id: 2
    :param p1: 116.481,39.989
    :param p2: 116.434,39.908
    :param start: 10:00
    :param city_name:
    :return:
    """
    if city_id is None and city_name is not None:
        city_id = get_city_code(city_name, 'city_name', 'id')
    one_travel_time = """
        select travel_time from travel_time
         where city_id='{0}' and p1::text='{1}' and p2::text='{2}' and travel_type='bus' and start={3} limit 1
    """
    try:
        p1_hex = Point(round(float(p1.split(',')[0]), 3), round(float(p1.split(',')[1]), 3)).wkb_hex
    except:
        p1_hex = ''
    try:
        p2_hex = Point(round(float(p2.split(',')[0]), 3), round(float(p2.split(',')[1]), 3)).wkb_hex
    except:
        p2_hex = ''
    start_hour = int(start.split(':')[0])
    time_sql = one_travel_time.format(city_id, p1_hex, p2_hex, start_hour)
    travel_time = None
    try:
        with conn.connect() as con:
            rows = con.execute(time_sql)
            row = rows.fetchone()
            if row is not None:
                travel_time = row['travel_time']
        logger.debug('get pg travel_time: ' + str(travel_time))
    except Exception as e:
        logger.exception(e)
    if travel_time is None:
        if city_name is None:
            city_name = get_city_value(city_id, False)
        no_exists_dicts = [{'origin': p1, 'destination': p2, 'city': city_name, 'cityd': city_name,
                            'time': start_hour, 'travel_type': 'bus', 'period': '0'}]
        # logger.debug('request amap:' + str(len(no_exists_dicts)))
        no_exists_json = list(map(json.dumps, no_exists_dicts))
        model_redis.lpush("disp_missing_point", *no_exists_json)
        travel_time, _ = location_to_time(p1, p2, city_id, start)
    return travel_time


if __name__ == '__main__':
    # df_orders, df_artisans = load_test_data()
    # df_points = gen_df_points(df_artisans)
    city_id = '2'
    p1 = '116.481, 39.989'
    p2 = '116.434, 39.908'
    start = '10:00'
    conn = conn_pgsql()
    t_time = get_travel_time(conn, city_id, p1, p2, start)
    print(t_time)
